package cn.piesat.dao;

import cn.piesat.dto.PiesatUserLogDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface PiesatUserLogDAO extends JpaRepository<PiesatUserLogDTO,String>, JpaSpecificationExecutor<PiesatUserLogDTO> {

    @Query(value = "select count(*) as value,mid(create_time,1,7) as name from piesat_user_log  where create_time between :beforeTime and :currentTime  and log_type = 1 group by name ORDER BY name asc;",nativeQuery = true)
    List<Object[]> getLoginMonthData(@Param("beforeTime")String beforeTime, @Param("currentTime")String currentTime);

    @Query(value = "select count(*) as value ,USERNAME as name from piesat_user_log WHERE CREATE_TIME between :beforeMonth and :currentTime and log_type = 1 group by name ORDER BY value desc limit 10",nativeQuery = true)
    List<Object[]> getLoginTopTen(@Param("beforeMonth")String beforeTime,@Param("currentTime")String currentTime);

    @Query(value = "select count(*) as value ,OPERATION as name from piesat_user_log WHERE CREATE_TIME between :beforeMonth and :currentTime and log_type = 2 group by name ORDER BY value desc limit 40",nativeQuery = true)
    List<Object[]> getBusiTopTen(@Param("beforeMonth")String beforeTime,@Param("currentTime")String currentTime);

    @Query(value = "select count(*) as value,l.USERNAME from piesat_user_log l left join piesat_user_userinfo ui on l.USERNAME = ui.USERNAME where l.LOG_TYPE = 1 group by l.USERNAME order by value desc limit 0,10",nativeQuery = true)
    List<Object[]> getLoginTopName();

}
